
#!/usr/bin/env python

import sys, re
from liwe.module import LiweModule

from os3.types.money import Money

class Course ( LiweModule ):
	def __init__ ( self, liwe ):
		super ( Course, self ).__init__ ( liwe, "course" )

		self.dbm = self.liwe.dbm
		self._settings =  self.get_settings()

	def send_mail ( self, sender, to, bcc , subject, html ):
		html = """<html><head><base href="%s"/></head><body>%s</body></html>"""  % ( self.liwe.base_url, html )
		sanitize = re.compile(r'<[^>]*>')
		text = sanitize.sub ( '', html )

		mail_sender = self.liwe.email ( sender, subject )
		mail_sender.to ( to )
		mail_sender.bcc ( bcc )
		mail_sender.text ( text )
		mail_sender.html ( html	)
		mail_sender.send ()

	def send_course_mail ( self, kind, id_reg_user ):
		reg_user = self.get_user ( id_user = id_reg_user )
		reg_user [ 'student_uid' ] = reg_user [ 'uid' ]
		#sys.stderr.write ( "\n SEND MAIL reg_user :%s \n\n"  % reg_user )

		profile = self.liwe.invoke ( 'address_book.ajax.get_profile' , uid = reg_user [ 'uid' ] ) [ 'profile' ]
		#sys.stderr.write ( "\n SEND MAIL profile :%s \n\n"  % profile )
		reg_user [ 'student_mail' ] = profile [ 'email' ]
		reg_user [ 'student_name' ] = profile [ 'name' ] 
		reg_user [ 'student_lastname' ] = profile [ 'lastname' ]

				
		 
		course = self.get_course ( id_course = reg_user [ 'id_course' ] )
		course [ 'course_note' ] = course.get (  'note' , '' )
		course [ 'course_descr' ] = course.get (  'descr' , '' )
		#sys.stderr.write ( "\n SEND MAIL course :%s \n\n"  % course )

		calendar = self.clear_dict ( self.get_calendar ( id_calendar = reg_user [ 'id_calendar' ] ) )
		calendar [ 'calendar_note' ] = calendar.get (  'note' , '' )
		#sys.stderr.write ( "\n SEND MAIL calendar :%s \n\n"  % calendar )
		calendar [ 'calendar_descr' ] = calendar.get (  'descr' , '' )


		charge = self.get_user_charge ( id_course = reg_user [ 'id_course' ] , id_calendar = reg_user [ 'id_calendar' ]  )
		dec_money  = Money ()
		dec_money.tsep = "."
		dec_money.dsep = ","
		for item in [ 'price_vat' , 'value' ]:
			dec_money.from_long_int ( charge [ item ] )
			charge  [ item ] = str ( dec_money )
		
			
		#sys.stderr.write ( "\n SEND MAIL charge :%s \n\n"  % charge )

		reg_user.update ( course )
		reg_user.update ( calendar )
		reg_user.update ( charge )
		#sys.stderr.write ( "\n SEND MAIL reg_user :%s \n\n"  % reg_user )

			
		#tpl = "NOTE_TRANSACTION_%s" % kind 
		#reg_user [ '_NOTE_TRANSACTION' ] = self.templates [ tpl ] % reg_user
		reg_user [ '_NOTE_TRANSACTION' ] = ""

		html =  self.templates [ 'SEND_MAIL_BASE' ] % reg_user
		
		sender = self.cfg.get ( "email.sender", "noreply@example.com" ) 
		to = reg_user [ 'student_mail' ]
		bcc = self.cfg.get ( "email.notify", "noreply@example.com" )
		#ADD TRAINER EMAIL
		trainer = self.liwe.invoke ( 'address_book.ajax.get_profile' , uid = calendar [ 'uid_trainer' ] )
		if trainer.get ( 'profile' ) :
			bcc.append ( trainer [ 'profile' ] [ 'email' ] )
			
	
		subject = "Iscrizione corso %s" %  reg_user [ 'name' ]

		self.send_mail ( sender, to, bcc, subject, html )
			
 
	def clear_dict ( self, dct ):
		res = dict()
		for k,v in dct.iteritems():
			if v : res [ k ] = v
		return res

		

	# OVERRAIDABLE #
	def list_course_manip ( self, row ):
		pass

	#{{{def get_settings ( self ):
	def get_settings ( self ):
		res = {}
		for k in [ 'trainer', 'student' ]:
			res [ k ] = self.cfg.get ( "group_id." + k, 0 )
		return res
	#}}}
	#{{{def get_user_charge ( self, id_course = 0, id_calendar = 0 ):
	def get_user_charge ( self, id_course = 0, id_calendar = 0 ):
		if not id_course or not id_calendar: return []
		tables = [ 'course_charge as ch', 'course_calendar ca' ]
		conds = []
		conds.append ( 'ch.id_course =%s' % id_course )  
		conds.append ( 'ca.id =%s' % id_calendar ) 
		conds.append ( 'day_before <= DATEDIFF ( ca.start_date, CURDATE() )' )
		fields = [ 'value', 'tax_code', 'product_coupon' ]
		extras = [ 'ORDER BY day_before DESC', 'LIMIT 1' ]

		res = self.dbm.single_row ( tables, fields, ' and '.join ( conds ), extras = ' '.join ( extras ), debug = True )

		if not res: return {} 

		vat =  1 +  ( float( res [ 'tax_code' ] )  / 100 )
		
		res [ 'price_vat' ] = round ( res [ 'value' ] * vat )


		return res		
	#}}}
	#{{{ COURSE 
	#{{{def set_course ( self, id_course = 0, name = '', descr = '', city = '', address = '',day_duration = 0, places = 0, visible = 1, note = '', tags = ''):
        def set_course ( self, id_course = 0, name = '', product_code = '', descr = '', city = '', address = '',day_duration = 0, places = 0,
			 visible = 1, note = '', tags = '', url = '', new_page = 0, desc_breve = '' ):

		values = { 'descr' : descr,
			   'name' : name,
			   'product_code' : product_code,
			   'note' : note,
			   'address' : address,
			   'city' : city,
			   'day_duration' : day_duration,
			   'places' :  places,
			   'visible' : visible,
			   'url' : url,
			   'new_page' : new_page,
			   'desc_breve' : desc_breve
                }

		if not id_course:
			values [ 'created' ] = '*NOW()'
			values [ 'uid' ] = self.liwe.pyhp.session.data.get ( "uid", 0 )
			self.dbm.insert ( 'course_course', values )
			id_course = self.dbm.insert_id ()	

		else:
			self.dbm.update ( "course_course", values, "id = %s" % id_course )

		values [ 'id_course' ] = id_course

		# save tags
		self.liwe.invoke ( "tags.admin.del_all_tags", id_obj = id_course, id_site = self.site_id, module = "course" )
		self.liwe.invoke ( "tags.ajax.tag_object", id_obj = id_course, id_site = self.site_id, tags = tags, module = "course" )


		return values
	#}}}
	#{{{def set_course_visible ( self, id_course, visible = 1 ):
	def set_course_visible ( self, id_course, visible = 1 ):
		if not id_course : return 
		values = { 'visible' : visible }
		self.dbm.update ( "course_course", values , "id = %s" % id_course )
	#}}}
	#{{{ get_course ( self, id_course = 0 ):
	def get_course ( self, id_course = 0 ):

		if not id_course:
			self.liwe.set_error ( '1', 'id_course value is not valid' )
			return [];
		
		vals = self.dbm.single_row ( 'course_course' , '*', 'id = %s' % id_course )

		if vals:
			tags = self.liwe.invoke ( "tags.ajax.tags_list", id_obj = vals [ "id" ], id_site = self.site_id, module = 'course' )
			vals [ "tags" ] = tags [ "tags" ]

		return vals
	#}}}
	#{{{ del_course ( self, id_course = ''):	
	def del_course ( self, ids = ''):
		
		if not ids:
			return [];

		
		
		_ids = ids.split ( '|' )
		_str_ids = ','.join ( _ids )


		self.dbm.delete ( "course_course", "id IN ( %s )" % _str_ids )
	
		#delete related tag
		for id in _ids:
			self.liwe.invoke ( "tags.ajax.del_all_tags", id_obj = id, id_site = self.site_id, module = 'course' )

		self.dbm.delete ( "course_charge", "id_course IN ( %s )" % _str_ids )
		self.dbm.delete ( "course_calendar", "id_course IN ( %s )" % _str_ids )
		self.dbm.delete ( "course_user", "id_course IN ( %s )" % _str_ids )

	#}}}
	#{{{def list_course ( self, name = '', descr = '', city = '',address = '', note = '',  visible = 0, day_duration = 0, tags = '', quant = 10, start_point = 0, do_count = 0 ):
	def list_course ( self, name = '', descr = '', city = '',address = '', note = '',  visible = -1, day_duration = 0, 
					 tags = '', nodates = 0, validdates = 0, quant = 10, start_point = 0, do_count = 0, incoming = 0 ):
		
		conds = [] 
		
		if name:			conds.append ( "name like '%%%s%%'"  % name )
		if city:			conds.append ( "city like '%%%s%%'"  % city )
		if address:			conds.append ( "address like '%%%s%%'"  % address )
		if note:			conds.append ( "note like '%%%s%%'"  % note )
		if descr:			conds.append ( "descr like '%%%s%%'"  % descr )
		if nodates:			conds.append ( "(select count(id) from course_calendar where id_course=course_course.id )=0" )
		if validdates:		conds.append ( "(select count(id) from course_calendar where id_course=course_course.id and start_date >= now())=0" )
		if incoming:		conds.append ( "(select count(id) from course_calendar where id_course=course_course.id and start_date >= now())>0" )
		if visible != -1 :	conds.append ( 'visible = %s' % visible )
		if day_duration:	conds.append ( 'day_duration = %s' % day_duration )	

		
		if tags:
			tags = self.liwe.invoke ( "tags.ajax.search",
						tag_names = tags.replace ( ",", "|" ),
						id_site = self.site_id, module = 'course', exp = 0 ) [ 'tags' ]

			if not tags: return { "rows" : [], "tot_rows" : 0 }
			conds.append ( "id IN (" + ", ".join ( t [ 'id_obj' ] for t in tags ) + ")" )

		
		tot_rows = 0
		if do_count:
			tot_rows = self.dbm.count ( 'course_course', [ 'id' ], ' AND '.join ( conds ) )


		rows = []
                _db_cur = self.dbm.select ( 'course_course', '*' , conds = ' and '.join ( conds )  )

                if not _db_cur: 
			rows = []
	                return { "rows" : rows, "tot_rows" : tot_rows }


		for row in _db_cur:
			self.list_course_manip ( row )
			rows.append ( row )
			
		
		_db_cur.close ()
		return { "rows" : rows, "tot_rows" : tot_rows }
	#}}}
	#}}}
	#{{{ CHARGE
	#{{{def set_charge ( self, id_charge = 0, id_course = 0, uid_trainer = 0, city = '', address = '',  descr = '', day_before = 0, value = 0, tax_code = 0 ):
	def set_charge ( self, id_charge = 0, id_course = 0,product_coupon = '', descr = '',
			 day_before = 0, value = 0, tax_code = 0 ):

		values = { 'id_course' : id_course,
			   'product_coupon' : product_coupon,
			   'descr' : descr,
			   'day_before' : day_before,
			   'value' :  value,
			   'tax_code' : tax_code }


		if not id_charge:
			values [ 'created' ] = '*NOW()'
			values [ 'uid' ] = self.liwe.pyhp.session.data.get ( "uid", 0 )
			self.dbm.insert ( 'course_charge', values )
			id_course = self.dbm.insert_id ()	

		else:
			self.dbm.update ( "course_charge", values, "id = %s" % id_charge )

		values [ 'id_charge' ] = id_charge
		return values
	#}}}
	#{{{def get_charge ( self, id_charge = 0 ):
	def get_charge ( self, id_charge = 0 ):
		if not id_charge:
			self.liwe.set_error ( '1', 'id_charge value is not valid' )
			return [];
		res =  self.dbm.single_row ( 'course_charge' , '*', 'id = %s' % id_charge )
		res [ 'end_value' ] = self.get_end_value ( res [ 'value' ] , res [ 'tax_code' ] )
		return res

	#}}}
	#{{{def get_end_value  ( self, value, tax_code ):
	def get_end_value  ( self, value, tax_code ):
		tax_code = 1 + ( float ( tax_code ) / 100 )
		value = float ( value )
		return long ( round ( value * tax_code ) )
	#}}}
	#{{{def del_charge ( self, ids = "" ):
	def del_charge ( self, ids = "" ):
		ids = ids.split ( "|" )
		str_ids = " ,".join ( ids )
		self.dbm.delete ( "course_charge", "id IN ( %s )" % str_ids )
	#}}}
	#{{{def list_charge ( self, id_course = 0, descr = '', quant = 10, start_point = 0, do_count = 0 ):
	def list_charge ( self, id_course = 0, uid_trainer = 0, city = '', address = '',descr = '', quant = 10, start_point = 0, do_count = 0 ):
		conds = []
		if id_course: conds.append ( "id_course = %s" % id_course )
		if uid_trainer: conds.append ( "uid_trainer = %s" % uid_trainer )
		if descr: conds.append ( "descr like '%%%s%%'" % descr )
		if city: conds.append ( "city like '%%%s%%'" % city )
		if address: conds.append ( "address like '%%%s%%'" % address )

		extras = 'ORDER BY day_before DESC '	
		tot_rows = self.dbm.count ( 'course_charge_view', 'id', ' AND '.join ( conds ) )
		if do_count:
			extras += " LIMIT %s,%s" % ( start_point, quant )
	
			
		resp = self.dbm.all_rows ( 'course_charge_view', '*', ' AND '.join ( conds ), extras = extras, debug = self.debug() )
		if not resp:
			resp = [] 
		return { "rows" : resp, "tot_rows" : tot_rows }
	#}}}
	#}}}
	#{{{CALENDAR
	#{{{def set_calendar ( self, id_calendar = 0, id_course = 0, uid_trainer = 0, city = '', address = '', start_date = '', descr = '' ):
	def set_calendar ( self, id_calendar = 0, id_course = 0, uid_trainer = 0, city = '', address = '', start_date = '', descr = '' ):
		values = { 'id_course' : id_course,
			   'uid_trainer' : uid_trainer,
			   'descr' : descr,
			   'city' : city,
			   'address' : address,
			   'descr' : descr,
			   'start_date' : start_date }

		if not id_calendar:
			values [ 'created' ] = '*NOW()'
			values [ 'uid' ] = self.liwe.pyhp.session.data.get ( "uid", 0 )
			self.dbm.insert ( 'course_calendar', values )
			id_calendar = self.dbm.insert_id ()	

		else:
			self.dbm.update ( "course_calendar", values, "id = %s" % id_calendar )

		values [ 'id_calendar' ] = id_calendar
		return values
	#}}}
	#{{{def get_calendar ( self, id_calendar = 0 ):
	def get_calendar ( self, id_calendar = 0 ):
		if not id_calendar:
			self.liwe.set_error ( '1', 'id_calendar value is not valid' )
			return [];
		return self.dbm.single_row ( 'course_calendar' , '*', 'id = %s' % id_calendar )
	#}}}
	#{{{def del_calendar ( self, ids = "" ):
	def del_calendar ( self, ids = "" ):
		if not ids:
			return [];
		ids = ids.split ( "|" )
		_str_ids = " ,".join ( ids )

		#check if calendar have some users subscribe
		count = self.dbm.count ( 'course_user', [ 'id' ] , " id_course in ( %s ) " % _str_ids )
		if count:
			self.liwe.set_error ( '1', "Ci sono altre tabelle che usano questo corso:%s" % '</br>'.join ( msg_err )  )
		self.dbm.delete ( "course_calendar", "id IN ( %s )" % _str_ids )
	#}}}
	#{{{def list_calendar ( self, mode = '', id_course = 0, start_date_from = '',  start_date_to = '', quant = 10, start_point = 0, do_count = 0 ):
	def list_calendar ( self, mode = '', id_course = 0, start_date_from = '',  start_date_to = '', quant = 10, start_point = 0, do_count = 0 ):
		conds = []
		if id_course: conds.append ( "id_course = %s" % id_course )
		if start_date_from: conds.append ( "start_date >= '%%%s%%'" % start_date_from )
		if start_date_to: conds.append ( "start_date <= '%%%s%%'" % start_date_to )
		if mode == 'valid':
			conds.append ( "start_date >=DATE ( NOW() )" )
		extras = 'ORDER BY start_date ASC '	
		if mode == 'incoming':
			conds.append ( "start_date >=DATE ( NOW() )" )
			extras = ' ORDER BY start_date '	
		tot_rows = self.dbm.count ( 'course_calendar_view', 'id', ' AND '.join ( conds ) )
		if do_count:
			extras += "LIMIT %s,%s" % ( start_point, quant )
	
			
		rows = []
		_db_cur = self.dbm.select (  'course_calendar_view', '*', ' AND '.join ( conds ), extras = extras )
		if not _db_cur: 
			rows = []
			return { "rows" : rows, "tot_rows" : tot_rows }
		
		conds = "( id_course, value ) in ( select id_course, max(value) from course_charge where id_course = %s)" % id_course 

		max_rate = self.dbm.single_row ( "course_charge", [ 'value as max_value', 'tax_code as max_tax_code' ] , conds )
		if max_rate :
			max_rate [ 'max_end_value' ] = self.get_end_value ( max_rate [ 'max_value' ] , max_rate [ 'max_tax_code' ] )	
		
	
		for row in _db_cur:
			charge = self.get_user_charge ( id_course = row [ 'id_course'] , id_calendar = row [ 'id' ] )
			if charge : row.update ( charge )
			if max_rate :row.update ( max_rate )
			rows.append ( row )
			
		
		_db_cur.close ()
		return { "rows" : rows, "tot_rows" : tot_rows }

	#}}}
	#}}}
	#{{{ USER
	#{{{def set_user ( self, uid = 0, id_calendar = 0, id_course = 0, subscription_date = '', pay = 0, paid = 0, note = '', description = '' ):
	def set_user ( self, id_user = 0, uid = 0, id_course = 0, id_calendar = 0, subscription_date = '', pay = 0, paid = 0, note = '', send_mail = '', transaction_kind = '' ):

		if not uid or not id_course or not id_calendar : return []
		values = {}
		values [ 'uid' ] = uid
		values [ 'id_course' ] = id_course
		values [ 'id_calendar' ] = id_calendar

		if uid and id_course and id_calendar and not id_user:
			conds = [ "%s = %s" % ( x , values [ x ]) for x  in values.keys() ]
			res = self.dbm.single_row ( 'course_user', [ 'id' ], ' and '.join ( conds ), debug = self.debug() ) 
			if res : id_user = res [ 'id' ]

		values [ 'subscription_date' ] = '*NOW()'
		if subscription_date : values [ 'subscription_date' ] = subscription_date
		if pay : values [ 'pay' ] = pay 
		if paid : values [ 'paid' ] = paid 
		if note : values [ 'note' ] = note 



		if not id_user:
			values [ 'created' ] = '*NOW()'
			self.dbm.insert ( 'course_user', values )
			id_user = self.dbm.insert_id ()	
		else:
			self.dbm.update ( 'course_user', values, 'id = %s' % id_user ) 

		values [ 'id_user' ] = id_user
		if send_mail: self.send_course_mail (transaction_kind, id_user ) 

		return values
	#}}}
	#{{{def get_user ( self, uid = 0, id_calendar = 0 ): 
	def get_user ( self, id_user = 0 ): 
		if not id_user:
			return [];
		return self.dbm.single_row ( 'course_user' , '*', 'id = %s' %  id_user )
	#}}}
	#{{{def del_user ( self, ids = "" ):
	def del_user ( self, ids = "" ):
		ids = ids.split ( "|" )
		str_ids = " ,".join ( ids )
		self.dbm.delete ( "course_user", "id IN ( %s )" % str_ids )
	#}}}
	def distinct_date_user ( self , id_course = 0 ):
		conds = ""
		if id_course : conds = "id_course=%s" % id_course
		extras = "order by start_date DESC;"
		return self.dbm.all_rows ( 'course_user_view', [ 'distinct ( start_date ) as start_date' , 'id_calendar' ] , conds, extras = extras ,debug = self.debug() )
		

	#}}}
	#{{{def list_user ( self, uid = 0, id_course = 0, id_calendar = 0, date_from = '', date_to = '', description = '' ,quant = 10, start_point = 0, do_count = 0 ):
	def list_user ( self, uid = 0, id_course = 0, id_calendar = 0, date_from = '', date_to = '', description = '' ,quant = 10, start_point = 0, do_count = 0 ):
		conds = []
		if uid: conds.append ( "uid = %s" % uid )
		if id_course: conds.append ( "id_course = %s" % id_course )
		if id_calendar: conds.append ( "id_calendar = %s" % id_calendar )
		if date_from: conds.append ( "subscription_date >= '%s'" % date_from )
		if date_to: conds.append ( "subscription_date <= '%s'" % date_to )
		if description: conds.append ( "description like '%%%s%%'" % description )

		extras = ''	
		tot_rows = self.dbm.count ( 'course_user_view', 'id', ' AND '.join ( conds ) )
		if do_count:
			extras = " LIMIT %s,%s" % ( start_point, quant )
	
			
		resp = self.dbm.all_rows ( 'course_user_view', '*', ' AND '.join ( conds ), extras = extras )
		if not resp:
			resp = [] 
		return { "rows" : resp, "tot_rows" : tot_rows }
	#}}}
	#{{{def list_teacher ( self ):
	def list_teacher ( self ):
		gid = self.cfg.get ( "group_id.trainer", 0 )
		res = self.liwe.invoke ( "user.group_admin.get_group_user", gid = gid )
		teachers = res.get ( 'users', [] )
		if not teachers: return []
		teachers = [ x for x in teachers if x [ 'enabled' ] == 1 ]
		return teachers
	#}}}
	#{{{def list_student ( self ):
	def list_student ( self ):
		res = self.liwe.invoke ( "user.admin.list" )
		students  = res.get ( 'users' , [] )		
		return students	
	#}}}
	#{{{def list_all ( self ):
	def list_all ( self ):
		teacher =  self.list_teacher()
		student =  self.list_student()
		return { 'teacher' : teacher, 'student' : student }
	#}}}
	#{{{def paypal_cback ( self, kwargs = {} ):
	def paypal_cback ( self, kwargs = {} ):
		
		for c in kwargs.courses:
			id_calendar = c.get ( 'id_calendar', 0 )
			id_course= c.get ( 'id_course', 0 )
			uid = kwargs.get ( 'uid', 0 )

			user = self.set_user ( id_course = id_course, id_calendar = id_calendar, uid = uid, send_mail = True, transaction_kind =  "PP" )

		#FIX PAY	
		#m_pay = Money ( kwargs.get ( 'mc_gross' , 0 ) )
		#pay = m_pay.to_long_int()
	#}}}
